﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QD.SQLHelper
{
    public class DbHelperSQL
    {
        public DbHelperSQL(string connectionString)
        {
            this.ConnectionString = connectionString;
        }

        public string ConnectionString { get; set; }

        public string DatabaseName
        {
            get
            {
                using (SqlConnection conn = GetConnection())
                {
                    return conn.Database;
                }
            }
        }

        private int _DbCommandTimeOut;
        /// <summary>
        /// 等待命令执行的时间（以秒为单位）。 默认值为 30 秒。
        /// </summary>
        public int DbCommandTimeOut
        {
            set { _DbCommandTimeOut = value; }
            private get { return _DbCommandTimeOut; }
        }

        private SqlTransaction _SqlTransaction;
        public SqlTransaction SqlTransaction
        {
            get
            {
                return _SqlTransaction;
            }
        }

        /// <summary>
        /// 是否有开启事务，如果开启了事务，则需手动关闭数据库
        /// </summary>
        private bool IsBeginTransaction = false;
        public void BeginTransaction()
        {
            SqlConnection conn = GetConnection();
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            _SqlTransaction = conn.BeginTransaction();
            IsBeginTransaction = true;
        }

        public void CloseTransaction()
        {
            _SqlTransaction = null;
            IsBeginTransaction = false;
        }

        public void Commit()
        {
            if (_SqlTransaction != null)
            {
                _SqlTransaction.Commit();
                CloseConnection();
            }
        }

        public void Rollback()
        {
            if (_SqlTransaction != null)
            {
                _SqlTransaction.Rollback();
                CloseConnection();
            }
        }

        private SqlConnection _SqlConnection;
        /// <summary>
        /// 返回连接
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetConnection()
        {
            if (_SqlConnection == null)
            {
                _SqlConnection = new SqlConnection(ConnectionString);
            }
            else if (string.IsNullOrEmpty(_SqlConnection.ConnectionString))
            {
                _SqlConnection.ConnectionString = ConnectionString;
            }

            return _SqlConnection;
        }

        /// <summary>
        /// 尝试关闭数据库
        /// </summary>
        private void TryCloseConnection()
        {
            if (!IsBeginTransaction && _SqlConnection != null)
            {
                CloseConnection();
            }
        }

        //关闭数据库
        private void CloseConnection()
        {
            if (_SqlConnection != null)
            {
                if (_SqlConnection.State != ConnectionState.Closed)
                {
                    _SqlConnection.Close();
                }
                _SqlConnection.Dispose();
            }
        }

        public object ExecuteScalar(string sql, params SqlParameter[] paramter)
        {
            SqlConnection conn = GetConnection();
            try
            {
                SqlCommand cmd = PrepareCommand(conn, SqlTransaction, sql, CommandType.Text, paramter);
                object result = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return result;
            }
            catch
            {
                throw;
            }
            finally
            {
                TryCloseConnection();
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string sql, params SqlParameter[] paramter)
        {
            SqlConnection conn = GetConnection();
            try
            {
                SqlCommand cmd = PrepareCommand(conn, SqlTransaction, sql, CommandType.Text, paramter);
                int rows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return rows;
            }
            catch
            {
                throw;
            }
            finally
            {
                TryCloseConnection();
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet Query(string sql, params SqlParameter[] paramter)
        {
            SqlConnection conn = GetConnection();
            try
            {
                SqlCommand cmd = PrepareCommand(conn, SqlTransaction, sql, CommandType.Text, paramter);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();

                    da.Fill(ds, "ds");
                    cmd.Parameters.Clear();

                    return ds;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                TryCloseConnection();
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>DataSet</returns>
        public DataSet RunProcedure(string storedProcName, SqlParameter[] parameters)
        {
            SqlConnection connection = GetConnection();
            try
            {
                DataSet dataSet = new DataSet();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = PrepareCommand(connection, SqlTransaction, storedProcName, CommandType.StoredProcedure, parameters);
                sqlDA.Fill(dataSet);
                connection.Close();
                return dataSet;
            }
            catch
            {
                throw;
            }
            finally
            {
                TryCloseConnection();
            }
        }

        private SqlCommand PrepareCommand(SqlConnection conn, SqlTransaction trans, string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlCommand cmd = new SqlCommand(cmdText, conn);
            if (DbCommandTimeOut > 0)
            {
                cmd.CommandTimeout = DbCommandTimeOut;
            }
            cmd.CommandType = cmdType;

            if (trans != null)
                cmd.Transaction = trans; ;

            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && parameter.Value == null)
                    {
                        parameter.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(parameter);
                }
            }

            return cmd;
        }

        /// <summary>
        /// 判断SQL是否正确
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ValidateSQL(string sql)
        {
            bool bResult;

            //验证时需开一个新的连接，因为原连接可能处于事务状态中
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SET PARSEONLY ON";
                cmd.ExecuteNonQuery();
                try
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    bResult = true;
                }
                catch
                {
                    bResult = false;
                }
                finally
                {
                    cmd.CommandText = "SET PARSEONLY OFF";
                    cmd.ExecuteNonQuery();
                }
            }

            return bResult;
        }
    }
}